﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;
using System.Configuration;
using System.Web.Configuration;
using Eagle.Config;
using System.IO;
using System.Web;

namespace Eagle.Data
{
    /// <summary>
    /// 数据访问工具类
    /// </summary>
    public class DbUtils
    {
        #region 变量定义
        private static Dictionary<string, DbProviderFactory> DbFactory = new Dictionary<string, DbProviderFactory>(2);
        #endregion

        #region 静态构造函数
        static DbUtils()
        {
            string configName = "web.config";
            Configuration config = null;
            if (File.Exists(AppDomain.CurrentDomain.BaseDirectory + configName))
                config = WebConfigurationManager.OpenWebConfiguration(string.Format("~/{0}", configName));
            //如果不是Web应用程序，则尝试Winform
            if (config == null)
                config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
            if (config != null)
            {
                ConnectionStringsSection section = config.ConnectionStrings;
                foreach (ConnectionStringSettings css in section.ConnectionStrings)
                {
                    if (!string.IsNullOrEmpty(css.ProviderName))
                    {
                        if (!DbFactory.ContainsKey(css.ProviderName))
                        {
                            DbProviderFactory dpf = DbProviderFactories.GetFactory(css.ProviderName);
                            DbFactory.Add(css.ProviderName, dpf);
                        }
                    }
                }
            }
           
        }
        #endregion

        #region 添加其他的数据库访问提供程序集实例
        /// <summary>
        /// 添加其他的数据库访问提供程序集实例（用于没有web.config或者app.config的情况）
        /// </summary>
        /// <param name="databaseProviderNameCollection">数据库访问提供程序集名称集合</param>
        public static void CreateOtherDatabaseProviderName(string[] databaseProviderNameCollection)
        {
            if (databaseProviderNameCollection == null || databaseProviderNameCollection.Length == 0)
                throw new ArgumentNullException("databaseProviderNameCollection", "参数不能为空");
            foreach (string providerName in databaseProviderNameCollection)
            {
                if (!string.IsNullOrEmpty(providerName))
                {
                    if (!DbFactory.ContainsKey(providerName))
                    {
                        DbProviderFactory dpf = DbProviderFactories.GetFactory(providerName);
                        DbFactory.Add(providerName, dpf);
                    }
                }
            }
        }
        #endregion

        #region 获取数据访问接口实例
        /// <summary>
        /// 获取数据访问接口实例
        /// </summary>
        /// <param name="providerName">数据访问接口程序集名称</param>
        /// <returns></returns>
        private static DbProviderFactory GetDbFactory(string providerName)
        {
            return DbFactory[providerName];
        }

        #endregion

        #region DbParameter实例化参数

        /// <summary>
        /// DbParameter,为方法执行添加参数,返回一个参数的实例，默认返回SQLServer的参数实例
        /// </summary>
        /// <param name="parameterName">参数名称</param>
        /// <param name="value">参数值</param>
        /// <returns></returns>
        public static DbParameter CreateParameter(string parameterName, object value)
        {
            return CreateParameter(parameterName, 0, value);
        }

        /// <summary>
        /// DbParameter,为方法执行添加参数,返回一个参数的实例，默认返回SQLServer的参数实例
        /// </summary>
        /// <param name="parameterName">参数名称</param>
        /// <param name="size">参数大小</param>
        /// <param name="value">参数值</param>
        /// <returns></returns>
        public static DbParameter CreateParameter(string parameterName, int size, object value)
        {
            return CreateParameter("System.Data.SqlClient", parameterName, size, value, ParameterDirection.Input);
        }

        /// <summary>
        /// DbParameter,为方法执行添加参数,返回一个参数的实例
        /// </summary>
        /// <param name="providerName">数据访问提供类名称</param>
        /// <param name="parameterName">参数名称</param>
        /// <param name="value">参数值</param>
        /// <param name="parmsDirection">参数的执行方向</param>
        /// <returns></returns>
        public static DbParameter CreateParameter(string providerName, string parameterName, object value, ParameterDirection parmsDirection)
        {
            return CreateParameter(providerName, parameterName, 0, value, parmsDirection);
        }

        /// <summary>
        /// DbParameter,为方法执行添加参数,返回一个参数的实例
        /// </summary>
        /// <param name="providerName">数据访问提供类名称</param>
        /// <param name="parameterName">参数名称</param>
        /// <param name="size">参数大小</param>
        /// <param name="value">参数值</param>
        /// <param name="parmsDirection">参数的执行方向</param>
        /// <returns></returns>
        public static DbParameter CreateParameter(string providerName, string parameterName, int size, object value, ParameterDirection parmsDirection)
        {
            DbParameter parameter = GetDbFactory(providerName).CreateParameter();
            parameter.ParameterName = parameterName;
            if (value == null)
                value = DBNull.Value;
            parameter.Value = value;
            if (size > 0)
                parameter.Size = size;
            parameter.Direction = parmsDirection;
            return parameter;
        }

        #endregion

        #region 打开关闭数据库连接方法
        /// <summary>
        /// 打开数据库连接
        /// </summary>
        /// <param name="conn"></param>
        private static void Open(DbConnection conn)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
        }
        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        /// <param name="conn"></param>
        private static void Close(DbConnection conn)
        {
            if (conn.State != ConnectionState.Closed)
                conn.Dispose();
        }
        #endregion

        #region 为Command命令添加参数
        /// <summary>
        /// 为Command命令添加参数
        /// </summary>
        /// <param name="parameters">参数列表</param>
        /// <param name="cmd">cmd命令</param>
        private static void AddParams(DbParameter[] parameters, DbCommand cmd)
        {
            if (parameters != null)
            {
                foreach (DbParameter p in parameters)
                    cmd.Parameters.Add(p);
            }
        }
        #endregion

        #region 返回DataReader
        /// <summary>
        /// 返回DataReader
        /// </summary>
        /// <param name="providerName">数据提供程序集名称</param>
        /// <param name="cmdText">cmdText语句</param>
        /// <param name="cmdType">cmdText语句类型</param>
        /// <param name="connString">数据库连接字符串,这个参数是为多个数据库而设计</param>
        /// <param name="parameters">参数列表</param>
        /// <returns></returns>
        public static DbDataReader ExecuteReader(string providerName, string cmdText, CommandType cmdType, string connString, DbParameter[] parameters)
        {
            DbConnection conn = GetDbFactory(providerName).CreateConnection();
            conn.ConnectionString = connString;
            using (DbCommand cmd = GetDbFactory(providerName).CreateCommand())
            {
                cmd.CommandText = cmdText;
                cmd.CommandType = cmdType;
                cmd.Connection = conn;
                AddParams(parameters, cmd);
                Open(conn);
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
        }

        /// <summary>
        /// 返回DataReader
        /// </summary>
        /// <param name="cmdText">cmdText语句</param>
        /// <param name="cmdType">cmdText语句类型</param>
        /// <param name="connString">数据库连接字符串,这个参数是为多个数据库而设计</param>
        /// <param name="parameters">参数列表</param>
        /// <returns></returns>
        public static DbDataReader ExecuteReader(string cmdText, CommandType cmdType, string connString, DbParameter[] parameters)
        {
            return ExecuteReader("System.Data.SqlClient", cmdText, cmdType, connString, parameters);
        }
        #endregion

        #region 返回受影响的行数，ExecuteNonQuery

        #region ExecuteNonQuery基础方法
        /// <summary>
        /// 返回受影响的行数,适用于：增删改，不用于查询！
        /// </summary>
        /// <param name="providerName">数据提供程序集名称</param>
        /// <param name="cmdText">cmdText语句</param>
        /// <param name="cmdType">cmdText语句类型</param>
        /// <param name="connString">数据库连接字符串,这个参数是为多个数据库而设计</param>
        /// <param name="timeOut">执行超时时间，单位（秒）</param>
        /// <param name="il">使用事务的级别</param>
        /// <param name="parameters">参数列表</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string providerName, string cmdText, CommandType cmdType, string connString, int timeOut, IsolationLevel il, DbParameter[] parameters)
        {
            DbConnection conn = null;
            DbCommand cmd = null;
            DbTransaction transaction = null;
            int returnCount = 0;
            try
            {
                conn = GetDbFactory(providerName).CreateConnection();
                cmd = GetDbFactory(providerName).CreateCommand();
                conn.ConnectionString = connString;
                cmd.CommandText = cmdText;
                cmd.CommandTimeout = timeOut;
                cmd.Connection = conn;
                cmd.CommandType = cmdType;
                AddParams(parameters, cmd);
                Open(conn);
                if (il != IsolationLevel.Unspecified)
                {
                    transaction = conn.BeginTransaction();
                    cmd.Transaction = transaction;
                }
                returnCount = cmd.ExecuteNonQuery();
                if (transaction != null)
                    transaction.Commit();
            }
            finally
            {
                if (transaction != null)
                    transaction.Dispose();
                Close(conn);
                if (cmd != null)
                {
                    cmd.Parameters.Clear();
                    cmd.Dispose();
                }
            }
            return returnCount;
        }

        #endregion

        #region 重载方法，你可以根据需要重载更多方法
        /// <summary>
        /// 返回受影响的行数,适用于：增删改，不用于查询！
        /// </summary>
        /// <param name="providerName">数据提供程序集名称</param>
        /// <param name="cmdText">cmdText语句</param>
        /// <param name="cmdType">cmdText语句类型</param>
        /// <param name="connString">数据库连接字符串,这个参数是为多个数据库而设计</param>
        /// <param name="il">使用事务的级别</param>
        /// <param name="parameters">参数列表</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string providerName, string cmdText, CommandType cmdType, string connString, IsolationLevel il, DbParameter[] parameters)
        {
            return ExecuteNonQuery(providerName, cmdText, cmdType, connString, 30, il, parameters);
        }

        /// <summary>
        /// 返回受影响的行数,适用于：增删改，不用于查询！,默认为SQLServer的访问方法
        /// </summary>
        /// <param name="cmdText">cmdText语句</param>
        /// <param name="cmdType">cmdText语句类型</param>
        /// <param name="connString">数据库连接字符串,这个参数是为多个数据库而设计</param>
        /// <param name="il">使用事务的级别</param>
        /// <param name="parameters">参数列表</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string cmdText, CommandType cmdType, string connString, IsolationLevel il, DbParameter[] parameters)
        {
            return ExecuteNonQuery("System.Data.SqlClient", cmdText, CommandType.Text, connString, il, parameters);
        }
        /// <summary>
        /// 返回受影响的行数,适用于：增删改，不用于查询！,默认为SQLServer的访问方法
        /// </summary>
        /// <param name="cmdText">cmdText语句</param>
        /// <param name="cmdType">cmdText语句类型</param>
        /// <param name="connString">数据库连接字符串,这个参数是为多个数据库而设计</param>
        /// <param name="parameters">参数列表</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string cmdText, CommandType cmdType, string connString, DbParameter[] parameters)
        {
            return ExecuteNonQuery("System.Data.SqlClient", cmdText, CommandType.Text, connString, IsolationLevel.Unspecified, parameters);
        }
        #endregion

        #endregion

        #region 返回一个字段，ExecuteScalar

        #region ExecuteScalar基础方法
        /// <summary>
        /// ExecuteScalar基础方法,返回首行首列,用于查询！
        /// </summary>
        /// <param name="providerName">数据提供程序集名称</param>
        /// <param name="cmdText">cmdText语句</param>
        /// <param name="cmdType">cmdText语句类型</param>
        /// <param name="connString">数据库连接字符串,这个参数是为多个数据库而设计</param>
        ///  <param name="timeOut">执行超时时间，单位（秒）</param>
        /// <param name="parameters">参数列表</param>
        /// <returns></returns>
        public static string ExecuteScalar(string providerName, string cmdText, CommandType cmdType, string connString, int timeOut, DbParameter[] parameters)
        {
            using (DbConnection conn = GetDbFactory(providerName).CreateConnection())
            {
                using (DbCommand cmd = GetDbFactory(providerName).CreateCommand())
                {
                    conn.ConnectionString = connString;
                    cmd.CommandText = cmdText;
                    cmd.CommandTimeout = timeOut;
                    cmd.Connection = conn;
                    cmd.CommandType = cmdType;
                    AddParams(parameters, cmd);
                    object outputValue = null;
                    Open(conn);
                    outputValue = cmd.ExecuteScalar();
                    if (cmd != null)
                    {
                        cmd.Parameters.Clear();
                    }
                    if (outputValue != null)
                        return outputValue.ToString().Trim(' ');
                    return string.Empty;
                }
            }
        }
        #endregion

        #region 重载1个ExecuteScalar方法，你可以根据需要重载更多方法

        /// <summary>
        /// ExecuteScalar基础方法,返回首行首列,用于查询！
        /// </summary>
        /// <param name="providerName">数据提供程序集名称</param>
        /// <param name="cmdText">cmdText语句</param>
        /// <param name="cmdType">cmdText语句类型</param>
        /// <param name="connString">数据库连接字符串,这个参数是为多个数据库而设计</param>
        /// <param name="parameters">参数列表</param>
        /// <returns></returns>
        public static string ExecuteScalar(string providerName, string cmdText, CommandType cmdType, string connString, DbParameter[] parameters)
        {
            return ExecuteScalar(providerName, cmdText, CommandType.Text, connString, 30, parameters);
        }

        /// <summary>
        /// ExecuteScalar,返回首行首列,用于查询！,默认为SQLServer的访问方法
        /// </summary>
        /// <param name="cmdText">cmdText语句</param>
        /// <param name="cmdType">cmdText语句类型</param>
        /// <param name="connString">数据库连接字符串,这个参数是为多个数据库而设计</param>
        /// <param name="parameters">参数列表</param>
        /// <returns></returns>
        public static string ExecuteScalar(string cmdText, CommandType cmdType, string connString, DbParameter[] parameters)
        {
            return ExecuteScalar("System.Data.SqlClient", cmdText, CommandType.Text, connString, parameters);
        }

        #endregion

        #endregion

        #region 返回一个数据表,GetDataTable

        #region 返回一个数据表,GetDataTable,基础方法
        /// <summary>
        /// 返回一个数据表,GetDataTable,基础方法
        /// </summary>
        /// <param name="providerName">数据提供程序集名称</param>
        /// <param name="cmdText">cmdText语句</param>
        /// <param name="cmdType">cmdText语句类型</param>
        /// <param name="connString">数据库连接字符串,这个参数是为多个数据库而设计</param>
        /// <param name="parameters">参数列表</param>
        /// <returns></returns>
        public static DataTable GetDataTable(string providerName, string cmdText, CommandType cmdType, string connString, DbParameter[] parameters)
        {
            using (DbConnection conn = GetDbFactory(providerName).CreateConnection())
            {
                using (DbCommand cmd = GetDbFactory(providerName).CreateCommand())
                {
                    conn.ConnectionString = connString;
                    cmd.CommandText = cmdText;
                    cmd.Connection = conn;
                    cmd.CommandType = cmdType;
                    AddParams(parameters, cmd);
                    using (DbDataAdapter dda = GetDbFactory(providerName).CreateDataAdapter())
                    {
                        dda.SelectCommand = cmd;
                        DataTable dt = new DataTable();
                        Open(conn);
                        dda.Fill(dt);
                        dda.SelectCommand.Parameters.Clear();
                        cmd.Parameters.Clear();
                        return dt;
                    }
                }
            }
        }
        /// <summary>
        /// 返回一个数据表,GetDataTable,获取分页数据的基础方法
        /// </summary>
        /// <param name="providerName">数据提供程序集名称</param>
        /// <param name="cmdText">cmdText语句</param>
        /// <param name="cmdType">cmdText语句类型</param>
        /// <param name="connString">数据库连接字符串,这个参数是为多个数据库而设计</param>
        /// <param name="totalCount">总记录数</param>
        /// <param name="totalPage">总页数</param>
        /// <param name="parameters">参数列表</param>
        /// <returns></returns>
        public static DataTable GetDataTable(string providerName, string cmdText, CommandType cmdType, string connString, out int totalCount, out int totalPage, params DbParameter[] parameters)
        {
            using (DbConnection conn = GetDbFactory(providerName).CreateConnection())
            {
                using (DbCommand cmd = GetDbFactory(providerName).CreateCommand())
                {
                    conn.ConnectionString = connString;
                    cmd.CommandText = cmdText;
                    cmd.Connection = conn;
                    cmd.CommandType = cmdType;
                    AddParams(parameters, cmd);
                    DbDataAdapter dda = GetDbFactory(providerName).CreateDataAdapter();
                    dda.SelectCommand = cmd;
                    DataTable dt = new DataTable();
                    Open(conn);
                    dda.Fill(dt);
                    object a = dda.SelectCommand.Parameters[0].Value;
                    object b = dda.SelectCommand.Parameters[1].Value;
                    if (a != null && b != null)
                    {
                        totalCount = int.Parse(a.ToString());
                        totalPage = int.Parse(b.ToString());
                    }
                    else
                    {
                        totalCount = 0;
                        totalPage = 0;
                    }
                    if (dda != null)
                    {
                        dda.SelectCommand.Parameters.Clear();
                        dda.Dispose();
                    }
                    if (cmd != null)
                        cmd.Parameters.Clear();
                    return dt;
                }
            }
        }
        #endregion

        #region 重载2个GetDataTable方法，你可以根据需要重载更多方法
        /// <summary>
        /// 返回一个数据表,GetDataTable,默认为SQLServer的访问方法
        /// </summary>
        /// <param name="cmdText">cmdText语句</param>
        /// <param name="cmdType">cmdText语句类型</param>
        /// <param name="connString">数据库连接字符串,这个参数是为多个数据库而设计</param>
        /// <param name="parameters">参数列表</param>
        /// <returns></returns>
        public static DataTable GetDataTable(string cmdText, CommandType cmdType, string connString, DbParameter[] parameters)
        {
            return GetDataTable("System.Data.SqlClient", cmdText, CommandType.Text, connString, parameters);
        }
        /// <summary>
        /// 返回一个数据表,GetDataTable,获取分页数据方法,默认为SQLServer的访问方法
        /// </summary>
        /// <param name="cmdText">cmdText语句</param>
        /// <param name="cmdType">cmdText语句类型</param>
        /// <param name="connString">数据库连接字符串,这个参数是为多个数据库而设计</param>
        /// <param name="totalCount">总记录数</param>
        /// <param name="totalPage">总页数</param>
        /// <param name="parameters">参数列表</param>
        /// <returns></returns>
        public static DataTable GetDataTable(string cmdText, CommandType cmdType, string connString, out int totalCount, out int totalPage, params DbParameter[] parameters)
        {
            return GetDataTable("System.Data.SqlClient", cmdText, cmdType, connString, out totalCount, out totalPage, parameters);
        }
        #endregion

        #endregion

        #region 返回一个数据集，GetDataSet

        #region 返回一个数据集,GetDataSet,基础方法
        /// <summary>
        /// 返回一个数据集,GetDataSet,基础方法
        /// </summary>
        /// <param name="providerName">数据提供程序集名称</param>
        /// <param name="cmdText">cmdText语句</param>
        /// <param name="cmdType">cmdText语句类型</param>
        /// <param name="connString">数据库连接字符串,这个参数是为多个数据库而设计</param>
        /// <param name="parameters">参数列表</param>
        /// <returns></returns>
        public static DataSet GetDataSet(string providerName, string cmdText, CommandType cmdType, string connString, DbParameter[] parameters)
        {
            using (DbConnection conn = GetDbFactory(providerName).CreateConnection())
            {
                using (DbCommand cmd = GetDbFactory(providerName).CreateCommand())
                {
                    conn.ConnectionString = connString;
                    cmd.CommandText = cmdText;
                    cmd.Connection = conn;
                    cmd.CommandType = cmdType;
                    AddParams(parameters, cmd);
                    DbDataAdapter dda = GetDbFactory(providerName).CreateDataAdapter();
                    dda.SelectCommand = cmd;
                    DataSet ds = new DataSet();
                    Open(conn);
                    dda.Fill(ds);
                    if (dda != null)
                    {
                        dda.SelectCommand.Parameters.Clear();
                        dda.Dispose();
                    }
                    if (cmd != null)
                        cmd.Parameters.Clear();
                    return ds;
                }
            }
        }
        #endregion

        #region 重载1个GetDataSet方法，你可以根据需要重载更多方法
        /// <summary>
        /// 返回一个数据集,GetDataSet,默认为SQLServer的访问方法
        /// </summary>
        /// <param name="cmdText">cmdText语句</param>
        /// <param name="cmdType">cmdText语句类型</param>
        /// <param name="connString">数据库连接字符串,这个参数是为多个数据库而设计</param>
        /// <param name="parameters">参数列表</param>
        /// <returns></returns>
        public static DataSet ExecuteDataSet(string cmdText, CommandType cmdType, string connString, DbParameter[] parameters)
        {
            return GetDataSet("System.Data.SqlClient", cmdText, CommandType.Text, connString, parameters);
        }
        #endregion

        #endregion
    }
}
